package lig.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import lig.com.conn.JdbcUtil;
import lig.com.dto.ClaimDTO;
import lig.com.dto.ClaimDTOList;

public class ClaimDAO {

private static ClaimDAO instance = new ClaimDAO();
	
	/**
	 * ************************************
	 * 1. Summary : get instance 
	 * ************************************
	 * @Method Name : getInstnace
	 * @return
	 */
	public static ClaimDAO getInstnace() {
		return instance;
	}	

	/**
	 * ************************************
	 * 1. Summary : run query with yyyy1, yyyy2, agent ID
	 * ************************************
	 * @Method Name : prmMonthYTD
	 * @param conn
	 * @param yyyy1
	 * @param yyyy2
	 * @param agentID
	 * @return
	 * @throws SQLException
	 */
	public ClaimDTOList claimMonthYTD(Connection conn, int yyyy1, int yyyy2, String agentID) throws SQLException {

		StringBuffer sb = new StringBuffer();
		
		ClaimDTOList ClaimDtoList = new ClaimDTOList();
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;
		
		try {		
			
			sb.append("SELECT AGENT, ACTDTE, LossInc, ExpInc ");
			sb.append("FROM ODS_LOSS.DBO.LossByAgent ");
			sb.append("WHERE LEFT(ACTDTE,4) in ");			
			sb.append("("+yyyy1+", "+yyyy2+") ");
			sb.append(" and AGENT = ");
			sb.append("'"+agentID+"' ");
			sb.append(" ORDER BY AGENT, ACTDTE");
			
			pstmt = conn.prepareStatement(sb.toString());
			
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				ClaimDTO ClaimDto = new ClaimDTO();				
				ClaimDto.setAgent(rs.getString("AGENT"));
				ClaimDto.setLoss(rs.getBigDecimal("LossInc"));
				ClaimDto.setExp(rs.getBigDecimal("ExpInc"));
				ClaimDto.setActdte(rs.getInt("ACTDTE"));	
				//System.out.println("PrmDto.getActDate : "+PrmDto.getActDate());
				ClaimDtoList.getDtoList().add(ClaimDto);
				
			}
			
		}	
		catch(Exception e){
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
		}
		return ClaimDtoList;
	
	}

}
